﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using FFS.IDAL;
using FFS.Model;
using FFS.DBUtility;

namespace FFS.SQLDAL
{
    public class FinanceItemDAO : IFinanceItemDAO
    {
        private const string FINANCE_ITEM_KEY = "@FinanceItemKey";
        private const string FINANCE_ITEM_NAME = "@FinanceItemName";
        private const string FINANCE_ITEM_REMARK = "@FinanceItemRemark";
        private const string INPUT_USER = "@input_user";
        private const string SELECT_FINANCE_ITEM_SQL = @" select FinanceItemID,FinanceItemKey,FinanceItemName,FinanceItemRemark,input_date,input_user from FinanceItem where is_deleted = 0 ";
        private const string CREATE_FINANCE_ITEM_SQL = @" insert into FinanceItem(FinanceItemKey,FinanceItemName,FinanceItemRemark,is_deleted,input_date,input_user) values(@FinanceItemKey,@FinanceItemName,@FinanceItemRemark,0,getdate(),@input_user)";
        private const string MODIFY_FINANCE_ITEM_SQL = @" update FinanceItem set FinanceItemName = @FinanceItemName,FinanceItemRemark = @FinanceItemRemark,input_date = getdate(),input_user = @input_user where FinanceItemKey = @FinanceItemKey and is_deleted = 0";
        private const string DELETE_FINANCE_ITEM_SQL = @" update FinanceItem set is_deleted = 1,input_date = getdate(),input_user = @input_user where FinanceItemKey = @FinanceItemKey and is_deleted = 0 ";

        /// <summary>
        /// 查询数据统一调用的函数
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        private List<FinanceItemInfo> GetQueryList(string sqlText, params SqlParameter[] paramArray)
        {
            List<FinanceItemInfo> listFinanceItem = new List<FinanceItemInfo>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(CommandType.Text,sqlText, paramArray))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        FinanceItemInfo fii = new FinanceItemInfo();
                        fii.financeItemID = reader.GetInt32(0);
                        fii.financeItemKey = reader.GetString(1);
                        fii.financeItemName = reader.GetString(2);
                        fii.financeItemRemark = reader.GetString(3);
                        fii.inputDate = reader.GetDateTime(4);
                        fii.inputUser = reader.GetString(5);
                        listFinanceItem.Add(fii);
                    }
                }
            }

            return listFinanceItem;
        }


        #region IFinanceItemDAO 成员

        public List<FinanceItemInfo> GetAllFinanceItem()
        {
            return GetQueryList(SELECT_FINANCE_ITEM_SQL, null);
        }

        public List<FinanceItemInfo> GetFinanceItemByItemKey(string itemKey)
        {
            SqlParameter param = new SqlParameter(FINANCE_ITEM_KEY, SqlDbType.NVarChar, 100);
            param.Value = "%"+itemKey+"%";
            string sqlText = SELECT_FINANCE_ITEM_SQL + " and FinanceItemKey like @FinanceItemKey  ";
            return GetQueryList(sqlText, param);
        }

        public List<FinanceItemInfo> GetFinanceItemByItemName(string itemName)
        {
            SqlParameter[] param = {
                                        new SqlParameter(FINANCE_ITEM_NAME,SqlDbType.NVarChar,500)
                                   };
            param[0].Value = "%"+itemName+"%";

            string sqlText = SELECT_FINANCE_ITEM_SQL + " and FinanceItemName like @FinanceItemName ";

            return GetQueryList(sqlText, param);
        }

        public List<FinanceItemInfo> GetFinanceItemBySearchCondition(string searchCondition)
        {
            string sqlText = SELECT_FINANCE_ITEM_SQL + " and " + searchCondition;

            return GetQueryList(sqlText,null);
        }

        public int CreateFinanceItem(FinanceItemInfo fii)
        {
            SqlParameter[] param = {
                                new SqlParameter(FINANCE_ITEM_KEY,SqlDbType.NVarChar,100),
                                new SqlParameter(FINANCE_ITEM_NAME,SqlDbType.NVarChar,500),
                                new SqlParameter(FINANCE_ITEM_REMARK,SqlDbType.NVarChar),
                                new SqlParameter(INPUT_USER,SqlDbType.NVarChar,50)
                             };
            param[0].Value = fii.financeItemKey;
            param[1].Value = fii.financeItemName;
            param[2].Value = fii.financeItemRemark;
            param[3].Value = "00000";
            return DBHelper.ExecuteNonQuery(CommandType.Text,CREATE_FINANCE_ITEM_SQL, param);
        }

        public int ModifyFinanceItem(FinanceItemInfo fii)
        {
            SqlParameter[] param = {
                                        new SqlParameter (FINANCE_ITEM_KEY,SqlDbType.NVarChar,100),
                                        new SqlParameter(FINANCE_ITEM_NAME,SqlDbType.NVarChar,500),
                                        new SqlParameter(FINANCE_ITEM_REMARK,SqlDbType.NVarChar),
                                        new SqlParameter(INPUT_USER,SqlDbType.NVarChar,50) 
                                     };
            param[0].Value = fii.financeItemKey;
            param[1].Value = fii.financeItemName;
            param[2].Value = fii.financeItemRemark;
            param[3].Value = "00000";
            return DBHelper.ExecuteNonQuery(CommandType.Text,MODIFY_FINANCE_ITEM_SQL, param);
        }

        public int DeleteFinanceItem(string itemKey)
        {
            SqlParameter[] param = 
                                    {
                                        new SqlParameter(FINANCE_ITEM_KEY, SqlDbType.NVarChar, 100),
                                        new SqlParameter(INPUT_USER,SqlDbType.NVarChar,50)
                                    };
            param[0].Value = itemKey;
            param[1].Value = "00000";

            return DBHelper.ExecuteNonQuery(CommandType.Text,DELETE_FINANCE_ITEM_SQL, param);
        }

        public bool IsExistsFinanceItem(string itemKey)
        {
            SqlParameter[] param = {
                                        new SqlParameter(FINANCE_ITEM_KEY,SqlDbType.NVarChar,100)
                                   };
            param[0].Value = itemKey;

            string sqlText = SELECT_FINANCE_ITEM_SQL + " and FinanceItemKey  = " + FINANCE_ITEM_KEY;

            if (DBHelper.ExecuteScalar(CommandType.Text, sqlText, param) == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion
    }
}
